<?php
/**
	Class used to connect to DataBase
*/
Class connexionDB 
{
	const PARAM_host	= 'localhost';
	const PARAM_port 	= '3306';
	const PARAM_db_name	= 'qualitymonitoring';
	const PARAM_user 	= 'root';
	const PARAM_db_pass	= 'showroom2014';

	var $DB;
	
	/**
		When this class is instantiate it automatically connect to the database
	*/
	function connexionDB()
	{
		try
		{
			//$this->DB = new PDO('mysql:host='. connexionDB::PARAM_host .';port=' . connexionDB::PARAM_port . 'dbname='. connexionDB::PARAM_db_name, connexionDB::PARAM_user, connexionDB::PARAM_db_pass);
			$this->DB = new PDO('mysql:host=localhost;dbname='.connexionDB::PARAM_db_name, 'root', '');
			$this->DB->exec("SET NAMES 'UTF8'");

		}
		catch(Exception $e)
		{
		    die('Erreur : '. $e->getMessage());
		}
	}

	/**
		Return all the email and the password from the table account
	*/
	function getDataFromAccount()
	{

		//$answer = $this->DB->query('SELECT * FROM `'.connexionDB::PARAM_db_name.'`.`account`');
		$answer = $this->DB->query('SELECT * FROM `'.connexionDB::PARAM_db_name.'`.`account`');
		$result = array();

		while ($data = $answer->fetch())
		{
			$result[] = $data;
		}

		//Always close the cursor when a query is treated
		$answer->closeCursor();
		return $result;
	}

	/**
		Return a listed groups from the DB
	*/
	function getListedGroup()
	{
		//$answer = $this->DB->query('SELECT * FROM `'.connexionDB::PARAM_db_name.'`.`group`');
		$answer = $this->DB->query('SELECT * FROM `'.connexionDB::PARAM_db_name.'`.`group` WHERE `group`.`active`=1');
		$result = array();

		if ($answer != NULL)
		{
			$result = $answer->fetchAll(PDO::FETCH_ASSOC);
			$answer->closeCursor();	
		}
		
		return $result;	
	}

	function listedTopic()
	{
		$answer = $this->DB->query('SELECT `id`, `description` as `name` FROM `'.connexionDB::PARAM_db_name.'`.`topic` WHERE `topic`.`active`=1');
		$result = array();

		while ($data = $answer->fetch())
		{
			$result[] = $data;
		}

		//Always close the cursor when a query is treated
		$answer->closeCursor();
		return $result;	
	}

	/**
		Return a listed account type from the DB
	*/
	function getListedAccountType()
	{
		//$answer = $this->DB->query('SELECT * FROM `'.connexionDB::PARAM_db_name.'`.`accountType`');
		$answer = $this->DB->query('SELECT * FROM `'.connexionDB::PARAM_db_name.'`.`accountType`');
		$result = array();

		while ($data = $answer->fetch())
		{
			$result[] = $data;
		}

		//Always close the cursor when a query is treated
		$answer->closeCursor();
		return $result;		
	}

	/**
		Return name of account type from id_accountType
	*/
	function getNameAccountType($idAccountType)
	{
		//$answer = $this->DB->query('SELECT `name` FROM `'.connexionDB::PARAM_db_name.'`.`account_type` WHERE `id`=' . $idAccountType);
		$answer = $this->DB->query('SELECT `name` FROM `'.connexionDB::PARAM_db_name.'`.`account_type` WHERE `id`=' . $idAccountType);
		$result = array();

		while ($data = $answer->fetch())
		{
			$result[] = $data;
		}

		$answer->closeCursor();
		return $result[0]['name'];
	}

	/**
		Add new criteria in DB
	*/
	function insertCriteria($criteria_name, $criteria_description, $criteria_coeff, $evaluation_id)
	{
		$query = "INSERT INTO `criteria` 
							(`name`,`description`,`coefficient`,`id_evaluation_form`) 
					VALUES 
							(:name, :description, :coefficient, :id_eval)";

		$parameters = array(':name' 		=> $criteria_name,
							':description'	=> $criteria_description,
							':coefficient' 	=> $criteria_coeff,
							':id_eval' 		=> $evaluation_id);
		$this->executePreparedQuery($query, $parameters);
		return $this->DB->lastInsertId();
	}

	/**
		Return the account type of an account
	*/
	function getAccountType($id_account)
	{
		$query = "SELECT 
						`account_type`.`id`, `account_type`.`name` 
					FROM 
						`account_type` LEFT JOIN 
						`account` ON `account`.`id_account_type`= `account_type`.`id`
					WHERE 
						`account`.`id`= :id_account";
		$parameters = array(':id_account' 	=> $id_account);

		return $this->executePreparedQuery($query, $parameters);
	}

	/**
		Return TRUE or FALSE if the mail adress already exist
	*/
	function isMailExist($email)
	{
		//$answer = $this->DB->query("SELECT email FROM `'.connexionDB::PARAM_db_name.'`.`account` WHERE `email` LIKE '$email'");
		$answer = $this->DB->query("SELECT email FROM `".connexionDB::PARAM_db_name."`.`account` WHERE `email` LIKE '$email'");
		$result = array();

		if ($answer != false)
		{
			while ($data = $answer->fetch())
			{
				$result[] = $data;
			}

			$answer->closeCursor();
			if (isset($result[0]['email']) && $result[0]['email'] != '')
			{
				return true;
			}
		}
		else
		{
			return false;
		}
	}

	/**
		Add New topic in DB (don't check if it's already exist)
	*/
	function addNewTopic($topic)
	{
		$query = 'INSERT INTO `'.connexionDB::PARAM_db_name.'`.`topic` (description) VALUES ("'. $topic .'")';
		$answer = $this->DB->query($query);
		if ($answer != NULL)
		{
			$answer->closeCursor();
		}
	}

	/**
		Delete topic
	*/
	function delTopic($id)
	{
		$query = 'UPDATE `'.connexionDB::PARAM_db_name.'`.`topic` 
										SET  `active`=0
										WHERE
											`topic`.`id`='. $id;
		$answer = $this->DB->query($query);
		if ($answer != NULL)
		{
			$answer->closeCursor();
		}
	}

	/**
		Insert in DB a new account 
	*/
	function addNewAccount($firstname, $lastname, $id_account_type, $email, $password, $id_groups, $id_country, $team)
	{
		$stmt = $this->DB->prepare("INSERT INTO 
										`".connexionDB::PARAM_db_name."`.`account` (
											`id`, 
											`firstname`, 
											`lastname`, 
											`id_account_type`, 
											`email`, 
											`password`, 
											`id_groups`, 
											`id_rights`, 
											`id_country`,
											`team`) 
									VALUES (
										NULL, 
										:firstname, 
										:lastname, 
										:id_account_type, 
										:email, 
										:password, 
										:id_groups, 
										NULL, 
										:id_country,
										:team)");
		$error = $stmt->execute(array(
			':firstname' => $firstname, 
			':lastname' => $lastname, 
			':id_account_type' => $id_account_type,
			':email' => $email,
			':password' => $password,
			':id_groups' => $id_groups,
			':id_country' => $id_country,
			':team' => $team
			));
	}

	/**
		Return the account data associated to an email
	*/
	function getDataFromEmail($email)
	{
		$query = "SELECT * FROM `account` WHERE `email` LIKE :email";
		$parameters = array(':email' => $email);
		$result = $this->executePreparedQuery($query, $parameters);
		if (isset($result[0]))
			return $result[0];
		else
			return array();
	}

	/**
		Execute a Query, and return an array(FETCH_ASSOC)
	*/
	function executeQuery($query)
	{
		$answer = $this->DB->query($query);
		$result = array();
		
		if ($answer == true)
		{
			while ($data = $answer->fetch(PDO::FETCH_ASSOC))
			{
				$result[] = $data;
			}
			if ($answer == NULL)
			{
				$answer->closeCursor();
				return array();
			}
		}

		return $result;
	}

	/**
		Update an account
	*/
	function updateAccount($account)
	{
		$stmt = $this->DB->prepare("UPDATE `".connexionDB::PARAM_db_name."`.`account` 
										SET  `firstname`=:firstname,
											`lastname`=:lastname,
											`id_account_type`=:id_account_type,
											`id_groups`=:id_groups,
											`id_country`=:id_country,
											`team`=:team
										WHERE
											`account`.`email` LIKE :email");
		$error = $stmt->execute(array(
				':firstname' => $account['firstname'],
				':lastname' => $account['lastname'],
				':id_account_type' => $account['id_account_type'],
				':id_groups' => $account['groups'],
				':id_country' => $account['country'],
				':team' => $account['team'],
				':email' => $account['email']
				));
	}

	/**
		Update just the password of an user
	*/
	function updatePassword($email, $password)
	{
		$answer = $this->DB->query("UPDATE `".connexionDB::PARAM_db_name."`.`account` 
										SET  `password`='". md5($password) ."'
										WHERE
											`account`.`email` LIKE '". $email ."'");
		$answer->closeCursor();
	}

	/**
		Delete an account
	*/
	function deleteAccount($email)
	{
		$answer = $this->DB->query("DELETE FROM `".connexionDB::PARAM_db_name."`.`account`
									WHERE `account`.`email` LIKE '". $email ."'");
		$answer->closeCursor();
	}

	/**
		Create form
	*/
	function addNewEvaluationForm($name, $date_created, $author)
	{
		$stmt = $this->DB->prepare("INSERT INTO `evaluation_form` (
									`id`,
									`name`, 
									`date_created`, 
									`author`) 
					VALUES (
									NULL, 
									:name,
									:date_created,
									:author)");
		$error = $stmt->execute(array(':name' => $name, ':date_created' => $date_created, ':author' => $author));
		return $this->DB->lastInsertId();
	}

	/**
		Return id form, return 0 if not found
	*/
	function getIdEvaluationForm($name, $date_created, $author)
	{
		$stmt = $this->DB->prepare("SELECT `id` 
									FROM `".connexionDB::PARAM_db_name."`.`evaluation_form` 
									WHERE 
										`name` LIKE :name AND
										`date_created` LIKE :date_created AND
										`author` LIKE :author
										LIMIT 1");
		$error = $stmt->execute(array(':name' => $name, ':date_created' => $date_created, ':author' => $author));
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		if (isset($result[0]['id']))
		{
			return $result[0]['id'];
		}
		return 0;
	}

	/**
		Return all the form saved in database
	*/
	function getEvaluationForm()
	{
		$query = 'SELECT `id`,`name` FROM `evaluation_form` WHERE `active` = 0';
		$result = $this->executeQuery($query);
		return $result;
	}

	/**
		Return id criteria by name, within a form
	*/
	function getIdCriteriaByName($name, $id_evaluation_form)
	{
		$query = "SELECT `id` FROM `criteria` WHERE `name` LIKE :name AND `id_evaluation_form` = :id_evaluation_form";
		$parameters = array(':name' => $name, ':id_evaluation_form' => $id_evaluation_form);
		$result = $this->executePreparedQuery($query, $parameters);
		return $result[0]['id'];		
	}
	/**
		add new criteria (!) : to modify, use prepared query & edit DB to latin_swedish
	*/
	function addCriteria($criteria, $id_evaluation_form)
	{
		$query = "INSERT INTO `".connexionDB::PARAM_db_name."`.`criteria` (
								`id`,
								`name`,
								`description`,
								`coefficient`,
								`id_evaluation_form`)
					VALUES ";
		$max = count($criteria);
		$i = 1;
		foreach ($criteria as $key => $value) {
			$name = str_replace('"', '\"', $value['name']);
			$name = str_replace("'", "\'", $name);
			$description = str_replace('"', '\"', $value['description']);
			$description = str_replace("'", "\'", $description);
			$query .= '(NULL,"'
						. $name . '","' 
						. $description . '",' 
						. $value['coeff'] . ',' 
						. $id_evaluation_form .')';
			if ($i < $max)
			{
				$query .= ',';
			}
			$i++;
		}
		
		$answer = $this->DB->query($query);
		if ($answer != NULL)
		{
			$answer->closeCursor();	
		}
	}

	/**
		Add the mark of a criteria (!) : to modify, use prepared query & edit DB to latin_swedish
	*/
	function addCriteriaMark($criteria)
	{
		$query = "INSERT INTO `".connexionDB::PARAM_db_name."`.`criteria_mark` (
								`id`,
								`id_criteria`,
								`id_evaluation_form`,
								`name`,
								`mark`,
								`description`)
					VALUES ";
		$max = count($criteria);
		$i = 1;
		foreach ($criteria as $key => $value)
		{
			$query .= '(NULL,'
						.$this->DB->quote($value['id_criteria']) .','
						.$this->DB->quote($value['id_evaluation_form']) .','
						.$this->DB->quote($value['name']) . ','
						.$this->DB->quote($value['mark']) . ','
						.$this->DB->quote($value['description']) . ')';
			if ($i < $max)
			{
				$query .= ',';
			}
			$i++;
		}
		$answer = $this->DB->query($query);
		if ($answer != NULL)
			$answer->closeCursor();
	}

	function addCriteriaForm($type, $criteria_name, $criteria_description, $criteria_coeff, $id_evaluation_form)
	{
		$query 		= "INSERT INTO `criteria` (`name`, `description`, `coefficient`, `id_evaluation_form`) 
							VALUES 
								(:name, :description, :coeff, :id_eval)";

		$parameters = array(':name' 		=> $criteria_name,
							':description'	=> $criteria_description,
							':coeff' 		=> $criteria_coeff,
							':id_eval'		=> $id_evaluation_form);
		$this->executePreparedQuery($query, $parameters);
	}

	/**
		return the data associate to a rating
	*/
	function getEvaluationData($id)
	{
		$query 		= 'SELECT * FROM `evaluation_form` WHERE `id`= :id';
		$parameters = array(':id' => $id);
		$result 	= $this->executePreparedQuery($query, $parameters);
		return ((isset($result[0])) ? $result[0] : $result);
	}

	/**
		Return criteria associate to an evaluation form
	*/
	function getCriteriaForEval($id)
	{
		$query 		= 'SELECT * FROM `criteria` WHERE `id_evaluation_form` = :id';
		$parameters = array(':id' => $id);
		$result 	= $this->executePreparedQuery($query, $parameters);
		return $result;
	}

	/**
		Return the marks & names of the criteria associate to a form
	*/
	function getLevelMark($id)
	{
		$query 		= 'SELECT `id`, `mark`,`name` FROM `criteria_mark` WHERE `id_evaluation_form` = :id GROUP BY `name` ORDER BY `mark` DESC';
		$parameters = array(':id' => $id);
		$result 	= $this->executePreparedQuery($query, $parameters);
		return $result;
	}

	/**
		Return the information of a criteria
	*/
	function getCriteriaMarkData($criteria, $mark, $idCriteria)
	{
        $query = "SELECT 
		                `criteria`.`name`,
		                `criteria_mark`.`id` as `id`, 
		                `criteria_mark`.`name`, 
		                `criteria_mark`.`description`,
		                `criteria_mark`.`mark`,
		                `criteria_mark`.`activate`
		            FROM 
		                `criteria`
		            LEFT JOIN 
		                `criteria_mark` 
		            ON 
		                `criteria`.`id` = `criteria_mark`.`id_criteria` 
		            WHERE 
		                `criteria`.`name` LIKE :criteria 
		                AND `criteria_mark`.`name` LIKE :mark
		                AND `criteria`.`id` = :id_criteria";
		$parameters = array(':criteria'		=> $criteria, 
							':mark' 		=> $mark,
							':id_criteria'	=> $idCriteria);
		/*echo $query;
		var_dump($parameters);*/
		$result = $this->executePreparedQuery($query, $parameters);
		if (isset($result[0]))
			return $result[0];
		return $result;
	}

	/**
		Add new group
	*/
	function addGroup($group)
	{
		$query 		= 'INSERT INTO `'.connexionDB::PARAM_db_name.'`.`group` (name) VALUES (:group)';
		$parameters = array(':group' => $group);
		$error = $this->executePreparedQuery($query, $parameters);
	}

	/**
		Add new "prestataire" WITHOUT checking if it already exist
	*/
	function addRecipient($recipient)
	{
		$stmt = $this->DB->prepare('INSERT INTO `'.connexionDB::PARAM_db_name.'`.`recipient` (name) VALUES (:new_recipient) ');
		$error = $stmt->execute(array(':new_recipient' => $recipient));
	}

	/**
		Delete group
	*/
	function delGroup($id)
	{
		$query = 'UPDATE `'.connexionDB::PARAM_db_name.'`.`group` 
										SET  `active`=0
										WHERE
											`group`.`id` = '. $id;
		$answer = $this->DB->query($query);
		if ($answer != NULL)
		{
			$answer->closeCursor();
		}
	}

	/**
		Delete recipient ('prestataire')
	*/
	function delRecipient($id)
	{
		$stmt = $this->DB->prepare('UPDATE `'.connexionDB::PARAM_db_name.'`.`recipient` SET `active`=0 WHERE `recipient`.`id`=:id_recipient');
		$error = $stmt->execute(array(':id_recipient' => $id));
	}

	/**
		Return unacceptable situation 	
	*/
	function getUnacceptable()
	{
		$query 	= 'SELECT * FROM `unacceptable` WHERE `unacceptable`.`active`=1';
		$result = $this->executeQuery($query);
		return $result;
	}

	/**
		Add new country WITHOUT checking if it's already exist in DB	
	*/
	function addCountry($name)
	{
		$stmt = $this->DB->prepare('INSERT INTO `'.connexionDB::PARAM_db_name.'`.`country` (name) VALUES (:country_name)');
		$stmt->execute(array(':country_name' => $name));
	}

	/**
		Delete a country
	*/
	function delCountry($id)
	{
		$stmt = $this->DB->prepare('UPDATE `'.connexionDB::PARAM_db_name.'`.`country` SET `active`=0 WHERE `country`.`id`=:id_country');
		$error = $stmt->execute(array(':id_country' => $id));
	}

	/**
		Return country 	id and name
	*/
	function getCountry()
	{
		$answer = $this->DB->query('SELECT `id`, `name` FROM `'.connexionDB::PARAM_db_name.'`.`country` WHERE `country`.`active`=1');
		$result = array();

		if ($answer != NULL)
		{
			$result = $answer->fetchAll(PDO::FETCH_ASSOC);
			$answer->closeCursor();	
		}
		return $result;	
	}

	/**
		Add unacceptable sitaution
	*/
	function addUnacceptable($description)
	{
		$query = 'INSERT INTO `'.connexionDB::PARAM_db_name.'`.`unacceptable` (description) VALUES ("'. $description .'")';
		$answer = $this->DB->query($query);
		if ($answer != NULL)
		{
			$answer->closeCursor();
		}
	}

	/**
		Delete unacceptable 	
	*/
	function delUnacceptable($id)
	{
		$query = 'UPDATE `'.connexionDB::PARAM_db_name.'`.`unacceptable` 
										SET  `active`=0
										WHERE
											`unacceptable`.`id` = '. $id;
		$answer = $this->DB->query($query);
		if ($answer != NULL)
		{
			$answer->closeCursor();
		}
		
	}

	/**
		Add in data base the new evaluation in the rating table	
		return the last id insert ID
	*/
	function addNewRating($data)
	{	
		$stmt = $this->DB->prepare('INSERT INTO 
										`'.connexionDB::PARAM_db_name.'`.`rating` (
											`date_call`,
											`date_eval`,
											`n_akio`,
											`n_order`,
											`name_rater`,
											`name_consultant`,
											`final_mark`,
											`optional_mark`,
											`improvement`,
											`id_topic`,
											`eval_type`,
											`groups`,
											`id_country`,
											`id_recipient`,
											`team`,
											`id_evaluation_form`,
											`validation`,
											`id_account_rater`) 
									VALUES (
										:date_call,
										:date_eval,
										:n_akio,
										:n_order,
										:name_rater,
										:name_consultant,
										:final_mark,
										:optional_mark,
										:improvement,
										:id_topic,
										:id_eval_type,
										:id_groups,
										:id_country,
										:id_recipient,
										:team,
										:id_evaluation_form,
										:validation,
										:id_account_rater
										)');
		$error = $stmt->execute(array(
										':date_call' 			=> $data['date_call'],
										':date_eval' 			=> $data['date_eval'],
										':n_akio' 				=> $data['n_akio'],
										':n_order' 				=> $data['n_order'],
										':name_rater' 			=> $data['name_rater'],
										':name_consultant' 		=> $data['name_consultant'],
										':final_mark' 			=> $data['final_mark'],
										':optional_mark' 		=> $data['optional_mark'],
										':improvement' 			=> $data['improvement'],
										':id_topic' 			=> $data['id_topic'],
										':id_eval_type' 		=> $data['id_eval_type'],
										':id_groups' 			=> implode(';', $data['id_groups']),
										':id_country' 			=> $data['id_country'],
										':id_recipient' 		=> $data['id_recipient'],
										':team' 				=> $data['team'],
										':id_evaluation_form' 	=> $data['id_evaluation_form'],
										':validation' 			=> $data['validation'],
										':id_account_rater' 	=> $data['id_account_rater']));
		return $this->DB->lastInsertId();
	}

	/**
		ADD in Data base the criteria associated with new rating must be used after'addNewRating'
	*/
	function addNewRatingCriteria($idRating, $listedCriteria)
	{
		foreach ($listedCriteria as $key => $value)
		{
			$stmt = $this->DB->prepare('INSERT INTO 
										`'.connexionDB::PARAM_db_name.'`.`rating_criteria` 
										(`id_criteria`, `id_rating`, `id_criteria_mark`, `id_unacceptable`, `commentary`) 
									VALUES (:id_criteria,
											:id_rating,
											:id_criteria_mark,
											:id_unacceptable,
											:commentary) ');
			$error = $stmt->execute(array(
										':id_criteria' => $key,
										':id_rating' => $idRating,
										':id_criteria_mark' => $value['id_mark_criteria'],
										':id_unacceptable' => $value['unacceptable'],
										':commentary' => $value['commentary']));
		}
	}

	/**
		return the list of recipient (prestataires)
	*/
	function getRecipient()
	{
		$query 		= "SELECT * FROM `recipient` WHERE `recipient`.`active`=1";
		$result 	= $this->executeQuery($query);
		return $result;
	}

	/**
		Return country name by id
	*/
	function getCountryName($id)
	{
		$stmt = $this->DB->prepare('SELECT `country`.`id`, `country`.`name` FROM `country` WHERE `country`.`id`=:id_country');
		$result = $stmt->execute(array(':id_country' => $id));
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		if (isset($result[0]))
		{
			return $result[0];
		}
		else
		{
			return array();
		}
	}

	/**
		Return recipient (prestataire) name by id
	*/
	function getRecipientName($id)
	{
		$stmt = $this->DB->prepare('SELECT `recipient`.`id`, `recipient`.`name` FROM `recipient` WHERE `recipient`.`id`=:id_recipient');
		$result = $stmt->execute(array(':id_recipient' => $id));
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		if (isset($result[0]))
		{
			return $result[0];
		}
		else
		{
			return array();
		}	
	}

	/**
		Return topic name by id
	*/
	function getTopicName($id)
	{
		$stmt = $this->DB->prepare('SELECT `topic`.`id`, `topic`.`description` FROM `topic` WHERE `topic`.`id`=:id_topic');
		$result = $stmt->execute(array(':id_topic' => $id));
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		if (isset($result[0]))
		{
			return $result[0];
		}
		else
		{
			return array();
		}	
	}

	/**
		Return All team in DB
	*/
	function getTeam()
	{
		$stmt = $this->DB->prepare('SELECT `rating`.`team` FROM `rating` WHERE 1 GROUP BY `rating`.`team`');
		$result = $stmt->execute();
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return $result;
	}

	/**
		Return id rater by name	
	*/
	function getIdRaterByName($name_rater)
	{
		$name_rater = explode(' ', $name_rater);
		$firstname = $name_rater[0];
		$lastname = $name_rater[1];

		$stmt = $this->DB->prepare('SELECT 
										`account`.`id` 
									FROM 
										`account`
									WHERE 
										`account`.`firstname` LIKE :firstname AND 
										`account`.`lastname` LIKE :lastname');

		$result = $stmt->execute(array(':firstname' => $firstname, ':lastname' => $lastname));
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		if (isset($result[0]['id']))
			return $result[0]['id'];
		else
			return 0;	
	}

	/**
		Return all the names of rater	
	*/
	function getNameRater()
	{
		$stmt = $this->DB->prepare('SELECT `rating`.`name_rater` FROM `rating` WHERE 1 GROUP BY `rating`.`name_rater`');
		$result = $stmt->execute();
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return $result;
	}

	/**
		Return all consultant name saved in table `rating`	
	*/
	function getConsultantName()
	{
		$query = 'SELECT `consultant`.`name` AS `name_consultant` FROM `consultant` WHERE 1';
		return $this->executeQuery($query);
	}

	/**
		Return a criteria list by id_rating
	*/
	function getCriteriaDescription($id)
	{
		$stmt = $this->DB->prepare('SELECT 
										`rating_criteria`.`id` AS `id_criteria`,
										`rating_criteria`.`commentary` AS `commentary`,
										`criteria`.`name` AS `name_criteria`,
										`criteria`.`coefficient` AS `coefficient_criteria`,
										`criteria_mark`.`name` AS `name_mark`,
										`criteria_mark`.`mark` AS `mark`,
										`criteria`.`set_to_zero`,
										`criteria`.`id` AS `original_id`
									FROM 
										`rating_criteria` 
									LEFT JOIN 
										`criteria` ON `rating_criteria`.`id_criteria` = `criteria`.`id`
									LEFT JOIN
										`criteria_mark` ON `criteria_mark`.`id` = `rating_criteria`.`id_criteria_mark`
									WHERE 
										`rating_criteria`.`id_rating` = :id_rating');

		$result = $stmt->execute(array(':id_rating' => $id));
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		if (isset($result))
		{
			return $result;
		}
		else
		{
			return array();
		}
	}

	/**
		add Calibration 
	*/
	function addCalibration($id_account, $nAkio, $nOrder, $groups, $country, $date_call)
	{
		$stmt = $this->DB->prepare("INSERT INTO 
										`".connexionDB::PARAM_db_name."`.`calibration` (
											`date`, 
											`id_account`,
											`n_akio`,
											`n_order`,
											`groups`,
											`id_country`,
											`date_call`) 
									VALUES (
										:today_date,
										:id_account,
										:n_akio,
										:n_order,
										:groups,
										:id_country,
										:date_call)");
		$error = $stmt->execute(array(
			':today_date' 	=> date('Y-m-d'),
			':id_account' 	=> $id_account,
			':n_akio' 		=> $nAkio,
			':n_order'		=> $nOrder,
			':groups' 		=> $groups,
			':id_country'	=> $country,
			':date_call'	=> $date_call
			));
		return $this->DB->lastInsertId();
	}

	/**
		associate id_calibration with a rating
	*/
	function linkRatingWithCalibration($listedID, $id_calibration)
	{
		foreach ($listedID as $id)
		{
			$stmt = $this->DB->prepare("INSERT INTO `".connexionDB::PARAM_db_name."`.`calibration_rating` (`id_calibration`,`id_rating`)
										VALUES (:id_calibration,:id)");
			$error = $stmt->execute(array(':id_calibration' => $id_calibration,
											':id' => $id));
		}
	}

	/**
		Return Id criteria 
	*/
	function getIdCriteriaByIdRatingCriteria($id)
	{
		$stmt = $this->DB->prepare("SELECT `id_criteria` FROM `rating_criteria` WHERE `rating_criteria`.`id`=:id");
		$error = $stmt->execute(array(':id' => $id));
		if ($error == true)
		{
			$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
			return $result[0];
		}
		return 0;
	}

	/**
		Return data of criteria
	*/
	function getCriteriaMarkById($id)
	{
		$query = "SELECT 
											`criteria_mark`.`id`, 
											`criteria_mark`.`id_criteria`, 
											`criteria_mark`.`id_evaluation_form`, 
											`criteria_mark`.`name`, 
											`criteria_mark`.`mark`, 
											`criteria`.`name` AS `criteriaName`
									FROM 
										`criteria_mark` 
									LEFT JOIN 
										`criteria` ON `criteria`.`id`=`criteria_mark`.`id_criteria` 
									WHERE 
										(`criteria_mark`.`id_criteria`=:id AND `criteria_mark`.`activate`= 1) OR `criteria_mark`.`name` LIKE 'Non applicable'
									GROUP BY 
										`criteria_mark`.`id_criteria`, `criteria_mark`.`id_evaluation_form`, `criteria_mark`.`name`, `criteria_mark`.`mark`
									ORDER BY 
										`criteria_mark`.`mark` DESC";

		$parameters = array(':id' => $id);
		$result = $this->executePreparedQuery($query, $parameters);
		return $result;
	}

	/**
		Udpate a criteria	
	*/
	function updateIdCriteria($id_criteria, $id_mark, $id_rating, $com)
	{
		$stmt = $this->DB->prepare('UPDATE `rating_criteria` SET `id_criteria_mark`=:id_mark, `commentary`=:commentary
									WHERE 
										`rating_criteria`.`id_criteria`=:id_criteria AND 
										`rating_criteria`.`id_rating`=:id_rating');
		$error = $stmt->execute(array(	':id_mark' => $id_mark,
										':id_criteria' => $id_criteria,
										':id_rating' => $id_rating,
										':commentary' => $com));
	}

	/**
		Return the mark & coefficient of a rating
	*/
	function getMarkAndCoeff($id_rating)
	{
		$stmt = $this->DB->prepare('SELECT `criteria_mark`.`mark`,`criteria`.`coefficient`, `criteria`.`set_to_zero`
										FROM `rating_criteria` 
										LEFT JOIN `criteria_mark` ON `criteria_mark`.`id` = `rating_criteria`.`id_criteria_mark` 
										LEFT JOIN `criteria` ON `criteria`.id=`criteria_mark`.`id_criteria` 
									WHERE `rating_criteria`.`id_rating`=:id_rating');

		$error = $stmt->execute(array(':id_rating' => $id_rating));
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return $result;
	}

	// To modify : create a table that can get the old final mark
	function updateFinalMark($final_mark, $id_rating)
	{
		$stmt = $this->DB->prepare('UPDATE `rating` SET `final_mark`=:final_mark, `optional_mark` = :final_mark
									WHERE 
										`rating`.`id`=:id_rating');
		$error = $stmt->execute(array(	':final_mark' => $final_mark,
										':id_rating' => $id_rating));	
	}

	/**
		Save the old mark in DB	
	*/
	function saveOldMark($old_mark, $id_rating)
	{
		$stmt = $this->DB->prepare('INSERT INTO `'.connexionDB::PARAM_db_name.'`.`old_mark` (`id_rating`, `old_mark`) VALUES (:id_rating, :old_mark)');

		$error = $stmt->execute(array(	':id_rating' 	=> $id_rating,
										':old_mark' 	=> $old_mark));
	}

	/**
		Return all the marks associate with a rating
	*/
	function getOldMark($id_rating)
	{
		$result = array();

		$stmt = $this->DB->prepare('SELECT `old_mark` FROM `'.connexionDB::PARAM_db_name.'`.`old_mark` WHERE `id_rating`=:id_rating');

		$error = $stmt->execute(array(	':id_rating' 	=> $id_rating));

		if ($error !== false)
		{
			$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		}
		return $result;
	}

	/**
		Update the last "Edit date" with the actual date
	*/
	function lastEditDate($id_rating)
	{
		$stmt = $this->DB->prepare('UPDATE `'.connexionDB::PARAM_db_name.'`.`rating` SET `date_last_edit`=:date_last_edit
									WHERE 
										`rating`.`id`=:id_rating');
		$error = $stmt->execute(array(	':date_last_edit' => date('Y-m-d'),
										':id_rating' => $id_rating));
	}

	/**
		Return groups by $id_account	
	*/
	function getGroupForIdAccount($id_account)
	{
		$stmt = $this->DB->prepare('SELECT `account`.`id_groups` FROM `account` WHERE `account`.`id`=:id');
		$error = $stmt->execute(array(':id' => $id_account));
		$result = array();
		$listedNameGrp  = array();

		if ($error != false)
		{
			$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if (isset($result[0]['id_groups']))
			{
				$listedGrp = explode(';', $result[0]['id_groups']);
				
				

				foreach ($listedGrp as $value) {
					if ($value != '')
					{
						$idGroup = $this->getGroupNameByID($value);
						if (isset($idGroup[0]['name']))
						{
							$listedNameGrp[] = array('id' => $value, 'name' => $idGroup[0]['name']);
						}
					}
				}
			}
		}
		return $listedNameGrp;
	}

	/**
		Return group name by ID	
	*/
	function getGroupNameByID($id)
	{
		$stmt = $this->DB->prepare('SELECT `name` FROM `'.connexionDB::PARAM_db_name.'`.`group` WHERE `group`.`id`=:id');
		$error = $stmt->execute(array(':id' => $id));
		$result = array();

		if ($error != false)
		{
			$result = $stmt->fetchAll(PDO::FETCH_ASSOC);	
		}
		return $result;
	}

	/**
		For statistics return the number of rating in DB
	*/
	function ratingNumber($date_start, $date_end, $id_country, $id_group, $team, $name_rater)
	{
		$group = $this->getGroupNameByID($id_group);
		$result = '';
		if (isset($group[0]['name']))
		{
			$group = $group[0]['name'];
			$stmt = $this->DB->prepare('SELECT
											COUNT(`rating`.`id`) AS `nb_rating`
										FROM
											`rating`
										WHERE 
											`rating`.`date_eval`>=:date_start AND `rating`.`date_eval` <= :date_end AND 
											`rating`.`id_country`=:id_country AND
											`rating`.`team` LIKE :team AND
											`rating`.`name_rater` LIKE :name_rater AND
											`rating`.`groups` LIKE :group');

			$error = $stmt->execute(array(	':date_start' 	=> $date_start,
											':date_end' 	=> $date_end,
											':id_country' 	=> $id_country,
											':team' 		=> $team,
											':name_rater' 	=> $name_rater,
											':group'		=> '%' . $group . '%'));	
			
			if ($error != false)
			{
				$result = $stmt->fetchAll(PDO::FETCH_ASSOC);	
			}
		}
		return $result;
	}

	/**
		Used to execute prepared query with their params	
	*/
	function executePreparedQuery($query, $parameters)
	{
		$stmt = $this->DB->prepare($query);
		$error = $stmt->execute($parameters);

		$result = array();

		if ($error != false)
		{
			$result = $stmt->fetchAll(PDO::FETCH_ASSOC);	
		}
		return $result;
	}

	/**
		Return all the calibration from DB
	*/
	function getCalibration()
	{
		$answer = $this->DB->query('SELECT 
										`calibration`.`id`, 
										`account`.`firstname`, 
										`account`.`lastname`, 
										`calibration`.`date`
									FROM 
										`calibration` LEFT JOIN 
										`account` ON `account`.`id`=`calibration`.`id_account`
									WHERE 1');
		$result = array();

		if ($answer != NULL)
		{
			$result = $answer->fetchAll(PDO::FETCH_ASSOC);
			$answer->closeCursor();	
		}
		
		return $result;	
	}

	/**
		Return the criteria mark by an id_rating
	*/
	function getCriteriaMarkByIdRating($id_rating)
	{
		$stmt = $this->DB->prepare('SELECT 
										`rating_criteria`.`id_rating`,
										`criteria`.`name`,
										`criteria_mark`.`mark` 
									FROM 
										`criteria_mark` 
										LEFT JOIN `rating_criteria` ON `rating_criteria`.`id_criteria_mark`=`criteria_mark`.`id` 
										LEFT JOIN `criteria` ON `criteria`.`id`=`rating_criteria`.`id_criteria`
									WHERE `rating_criteria`.`id_rating`=:id_rating ORDER BY `criteria`.`name`');

		$error = $stmt->execute(array(':id_rating' => $id_rating));
		$result = array();

		if ($error != false)
		{
			$result = $stmt->fetchAll(PDO::FETCH_ASSOC);	
		}
		return $result;
	}

	/**
		Return the list of rating associate to a Calibration
	*/
	function getIdRatingByIdCalibration($id_calibration)
	{
		$stmt = $this->DB->prepare('SELECT `calibration_rating`.`id_rating` FROM `calibration_rating` WHERE `calibration_rating`.`id_calibration`=:id_calibration');

		$error = $stmt->execute(array(':id_calibration' => $id_calibration));
		$result = array();

		if ($error != false)
		{
			$result = $stmt->fetchAll(PDO::FETCH_ASSOC);	
		}
		return $result;
	}

	/**
		DB 5 valeurs dans le champ "progress" :
			- 0%
			- 25%
			- 50%
			- 75%
			- 100%
		Return data for a rating by id account
	*/
	function getRatingForNotification($group)
	{
		$month 		= date('n');
		
		$query = 'SELECT 
						`rating`.`id`,
						`rating`.`progress`,
						`rating`.`name_consultant`,
	                    `rating`.`name_rater`,
	                    `rating`.`date_eval`
	                FROM 
	                	`rating` 
					WHERE 
						`rating`.`groups` LIKE :group AND 
						`rating`.`progress` != 100 AND 
						`rating`.`progress` != 50 AND 
	                    `rating`.`final_mark` < 50 AND 
	                    MONTH(`rating`.`date_eval`) =:month';
	    
	    $parameters = array(':group' 		=> $group,
	    					':month'		=> $month);
        return $this->executePreparedQuery($query, $parameters);
	}

	/**
		Once a manager send a debrief a notification toggle to the person that did the evaluation
	*/
	function getRatingForNotificationAnswered($id_account)
	{
		$month 		= date('n');
		
		$query = 'SELECT 
						`rating`.`id`,
						`rating`.`progress`,
						`rating`.`name_consultant`,
	                    `rating`.`name_rater`,
	                    `rating`.`date_eval`
	                FROM 
	                	`rating` 
					WHERE 
						`rating`.`id_account_rater` LIKE :id_account AND 
						`rating`.`progress` = 50 AND
	                    MONTH(`rating`.`date_eval`) = :month';

	    $parameters = array(':id_account' 	=> $id_account,
	    					':month'		=> $month);
        return $this->executePreparedQuery($query, $parameters);
	}

	/**
		Add Debrief in DB
	*/
	function insertDebrief($data)
	{
		$query 		= "SELECT `debriefing`.`id` FROM `debriefing` WHERE `debriefing`.`id_rating` = :id_rating";
		$parameters = array(':id_rating'	=> $data['id_rating']);
		$id_debrief = $this->executePreparedQuery($query, $parameters);
		
		if (isset($id_debrief[0]['id']))
		{
			$query = "UPDATE `debriefing` 
						SET  `finding`=:finding,
							`diagnosis`=:diagnosis,
							`back`=:back,
							`action_plan`=:action_plan
						WHERE
							`debriefing`.`id_rating` LIKE :id_rating";
		}
		else
		{
			$query =  "INSERT INTO 
						`debriefing` (
						`finding`, 
						`diagnosis`, 
						`back`, 
						`action_plan`, 
						`id_rating`) 
					VALUES (
						:finding, 
						:diagnosis, 
						:back, 
						:action_plan, 
						:id_rating)";
		}
		

		$parameters = array(':finding' 		=> $data['finding'],
							':diagnosis' 	=> $data['diagnosis'],
							':back'			=> $data['back'],
							':action_plan'	=> $data['action_plan'],
							':id_rating'	=> $data['id_rating']);

		$this->executePreparedQuery($query, $parameters);
	}


	/**
		Return debrief associate to a rating
	*/
	function getDebrief($id_rating)
	{
		$query = "SELECT * FROM `debriefing` WHERE `debriefing`.`id_rating` = :id_rating";
		$parameters = array(':id_rating' 	=> intval($id_rating));

		return $this->executePreparedQuery($query, $parameters);
	}


	/**
		Update the workflow progress of a rating
	*/
	function updateProgress($id_rating, $nb)
	{
		$query = "UPDATE `rating` 
					SET  `rating`.`progress` = :progress
					WHERE
						`rating`.`id` LIKE :id_rating";

		$parameters = array(':id_rating' 	=> $id_rating,
							':progress' 	=> $nb);

		$this->executePreparedQuery($query, $parameters);
	}

	/**
		Return the groups id of an account
	*/
	function getGroupByIdAccount($id_account)
	{
		$query = "SELECT `account`.`id_groups` FROM `account` WHERE `account`.`id`= :id_account";
		$parameters = array(':id_account' 	=> $id_account);

		return $this->executePreparedQuery($query, $parameters);
	}

	/**
		Set debrief to 1
	*/
	function updateDebrief($id_rating)
	{
		$query = "UPDATE `rating` 
					SET  `rating`.`reject_debrief` = 1
					WHERE
						`rating`.`id` LIKE :id_rating";

		$parameters = array(':id_rating' 	=> $id_rating);
		$this->executePreparedQuery($query, $parameters);
	}

	/**
		Retune list of manager
	*/
	function getManagerList()
	{
		$result = $this->executeQuery('SELECT `account`.`id`, `account`.`firstname`, `account`.`lastname` FROM `account` WHERE `account`.`id_account_type` = 2');
		return $result;
	}

	/**
	*/
	function getMailAdress()
	{
		$result = $this->executeQuery('SELECT * FROM `associated_mail` WHERE 1');
		return $result;	
	}

	function getMailAdressActivated()
	{
		$result = $this->executeQuery('SELECT * FROM `associated_mail` WHERE `associated_mail`.`active` = 1');
		return $result;
	}

	/**
		Insert file path & name of the uploaded file
	*/
	function uploadXmlFile($country_name, $path, $exist)
	{
		if ($exist == 0)
		{
			$query = "INSERT INTO `translate` (`name`, `path`)
							VALUES (:name, :path_file)";
		}
		else
		{
			$query = "UPDATE `translate` 
						SET  `translate`.`path` = :path_file
						WHERE
							`translate`.`name` LIKE :name";
		}

		$parameters = array(':name' 		=> $country_name,
							':path_file'	=> $path);

		$this->executePreparedQuery($query, $parameters);
	}

	/**
		Return all the language avaible, saved in DB
	*/
	function getFilePath()
	{
		$result = $this->executeQuery('SELECT * FROM `translate` WHERE 1');
		return $result;
	}

	/**
		Return a hebdo rating report done by admins, associate to a group
	*/
	function getWeeklyReport($monday, $tuesday, $wednesday, $thurday, $friday, $group)
	{
		$query = "SELECT 
					`rating`.`id`,
					`rating`.`date_eval`,
					`rating`.`name_consultant`, 
					`rating`.`final_mark`, 
					`rating`.`groups`
				FROM 
					`rating` LEFT JOIN `account` ON `account`.`id`=`rating`.`id_account_rater` 
				WHERE 
					(	`rating`.`date_eval` = :monday OR 
						`rating`.`date_eval` = :tuesday OR 
						`rating`.`date_eval` = :wednesday OR 
						`rating`.`date_eval` = :thurday OR 
						`rating`.`date_eval` = :friday ) AND 
					`account`.`id_account_type` = 1 AND
					`rating`.`groups` LIKE :group 
				GROUP BY 
					`rating`.`date_eval`, `rating`.`name_consultant`
				ORDER BY 
					`rating`.`name_consultant`";

		$parameters = array(	':monday' 		=> $monday, 
								':tuesday' 		=> $tuesday,
								':wednesday' 	=> $wednesday,
								':thurday' 		=> $thurday,
								':friday' 		=> $friday,
								':group' 		=> $group);

		$result = $this->executePreparedQuery($query, $parameters);
		return $result;
	}

	/**
		Return associated mail with group
	*/
	function getMailAssociatedToIdGroup($id_group)
	{
		$query = "SELECT 
					`associated_mail`.`mail`, 
					`associated_mail`.`groups`
				FROM
					`associated_mail`
				WHERE
					`associated_mail`.`groups` LIKE :id_group";

		$parameters = array(':id_group' => $id_group);
		$result = $this->executePreparedQuery($query, $parameters);
		return $result;
	}

	/**
		Delete row associate to group
	*/
	function deletedRowAssociated($id_group)
	{
		$id_group_like 	= '%'. $id_group . '%';
		
		$query 		= "SELECT `associated_mail`.`mail`, `associated_mail`.`groups` 
						FROM `associated_mail` 
						WHERE `associated_mail`.`groups` LIKE :groups";
		
		$parameters = array(':groups' => $id_group_like);
		$mail_list 	= $this->executePreparedQuery($query, $parameters);

		foreach ($mail_list as $mail)
		{
			$old_groups = explode(';', $mail['groups']);

			foreach ($old_groups as $key => $group) 
			{
				if ($group != '' && $group == $id_group)
				{
					unset($old_groups[$key]);
				}
			}

			$query 		= "UPDATE `associated_mail`
							SET  `associated_mail`.`groups`= :new_groups
							WHERE `associated_mail`.`mail` LIKE :mail";
			
			$parameters = array(':new_groups' 	=> implode(';', $old_groups), 
								':mail' 		=> $mail['mail']);
			$this->executePreparedQuery($query, $parameters);
		}
	}

	/**
		Update or insert new mail in DB
	*/
	function updateAssocitedMailToGroup($mail, $id_group)
	{
		$query 			= "SELECT * FROM `associated_mail` WHERE `mail` LIKE :mail";
		$parameters 	= array(':mail' => $mail);
		$result 		= $this->executePreparedQuery($query, $parameters);

		if (isset($result[0]['groups']))
		{
			$grpStr = $result[0]['groups'];
			if (strpos($grpStr, $id_group) !== false)
			{
				echo 'In BDD';
			}
			else
			{
				$grpStr .= $id_group . ';';
				$query = "UPDATE `associated_mail` 
							SET  `associated_mail`.`groups` = :groups
							WHERE
								`associated_mail`.`mail` LIKE :mail";
				$parameters = array(':groups' 	=> $grpStr,
									':mail' 	=> $mail);
				$this->executePreparedQuery($query, $parameters);
			}
		}
		else
		{
			$query 			= 'INSERT INTO `associated_mail` (mail, groups) VALUES (:mail , :group)';
			$parameters		= array(':mail' 	=> $mail,
									':group' 	=> $id_group . ';');
			$this->executePreparedQuery($query, $parameters);
		}
	}

	/**
		Delete from db evaluation form
	*/
	function deleteEvaluationRating($id_evaluation_form)
	{
		$query = "UPDATE  `evaluation_form` SET `evaluation_form`.`active` = 1 WHERE `evaluation_form`.`id` = :id_evaluation_form";
		$params = array(':id_evaluation_form' => $id_evaluation_form);
		$this->executePreparedQuery($query, $params);
	}

	/**
		Rename evaluation form
	*/
	function renameEvalRating($id_eval, $rename)
	{
		$query = "UPDATE `evaluation_form` SET `evaluation_form`.`name` = :rename WHERE `evaluation_form`.`id` = :id_eval";
		$params = array(':rename' => $rename, ':id_eval' => $id_eval);
		$this->executePreparedQuery($query, $params);
	}

	/**
		Return mail saved in DB
	*/
	function getSavedMail()
	{
		$query = "SELECT 
					*
				FROM 
					`mail`";

		$result = $this->executeQuery($query);
		if (isset($result[0]))
		{
			return $result[0];	
		}
		else
		{
			return array();
		}		
	}

	/**
		reinit adress mail
	*/
	function updateActiveMail()
	{
		$query 		= "UPDATE `associated_mail` SET `associated_mail`.`active` = 0 WHERE 1";
		
		$this->executeQuery($query);
	}

	/**
		Update mail
	*/
	function updateMail($send_to, $new_subject, $mail_content)
	{
		$this->updateActiveMail();
		foreach ($send_to as $mail)
		{
			if ($mail != null)
			{
				$query 			= 'SELECT `associated_mail`.`id` FROM `associated_mail` WHERE `associated_mail`.`mail` LIKE :mail';
				$parameters 	= array(':mail' => $mail);
				$result 		= $this->executePreparedQuery($query, $parameters);

				if (isset($result[0]['id']))
				{
					$query 		= 'UPDATE `associated_mail` SET `associated_mail`.`active` = 1 WHERE `associated_mail`.`id`=:id_associated_mail';
					$parameters = array(':id_associated_mail' => $result[0]['id']);
					$this->executePreparedQuery($query, $parameters);
				}
				else
				{
					$query 		= 'INSERT INTO `associated_mail` (`id`, `mail`, `active`) VALUES (NULL, :mail, 1)';
					$parameters = array(':mail' => $mail);
					$this->executePreparedQuery($query, $parameters);	
				}
			}
		}

		$query = "UPDATE `mail` 
					SET 
						`mail`.`mail_subject` 	= :mail_subject,
						`mail`.`mail_body` 		= :mail_content
					WHERE 
						`mail`.`id` = 1";

		$parameters = array(':mail_subject' => $new_subject,
							':mail_content' => $mail_content);

		$error = $this->executePreparedQuery($query, $parameters);
		if ($error != false)
		{
			echo 'ko';
		}
		else
		{
			echo 'ok';
		}
	}

	/**
		return not applicable id criteria_mark
	*/
	function getIdCriteriaMark()
	{
		$query = "SELECT * FROM `criteria_mark` WHERE `name` LIKE 'Non applicable'";
		$result = $this->executeQuery($query);

		return $result;
	}

	/**
		Update criteria
	*/
	function updateCriteria($id_criteria, $criteria_name, $criteria_description, $coefficient)
	{
		$query 		= "UPDATE `criteria` 
						SET 
							`criteria`.`name` 			= :criteria_name, 
							`criteria`.`description`	= :criteria_description, 
							`criteria`.`coefficient`	= :coefficient 
						WHERE 
							`criteria`.`id` = :id_criteria";

		$parameters = array(':criteria_name' 		=> $criteria_name,
							':criteria_description' => $criteria_description,
							':coefficient' 			=> $coefficient,
							':id_criteria'			=> $id_criteria);
		$this->executePreparedQuery($query, $parameters);
	}

	function getLevelMarkDescriptionByIdCriteria($id_criteria)
	{
		$query = "SELECT `id`, `name`, `description`, `id_criteria`, `activate` FROM `criteria_mark` WHERE `id_criteria` = :id_criteria ORDER BY `mark` DESC";
		$parameters = array(':id_criteria' => $id_criteria);
		return $this->executePreparedQuery($query, $parameters);
	}

	/**
		Update criteria mark
	*/
	function updateCriteriaMark($mark_id, $mark_description, $checked)
	{
		$query = "UPDATE `criteria_mark`
					SET
						`criteria_mark`.`description` 	= :description,
						`criteria_mark`.`activate`		= :checked
					WHERE 
						`criteria_mark`.`id` = :id_mark";

		$parameters = array(':description' 	=> $mark_description,
							':id_mark' 		=> $mark_id,
							':checked' 		=> $checked);
		$this->executePreparedQuery($query, $parameters);
	}

	/**
		Update level Mark 
	*/
	function updateLevelMark($name, $mark, $id_eval)
	{
		$query = "UPDATE `criteria_mark`
					SET 
						`criteria_mark`.`name` = :name,
						`criteria_mark`.`mark` = :mark
					WHERE 
						`criteria_mark`.`name` LIKE :name AND
						`criteria_mark`.`id_evaluation_form` = :id_eval";

		$parameters = array(':name' 	=> $name,
							':mark' 	=> $mark,
							':id_eval' 	=> $id_eval);
		$this->executePreparedQuery($query, $parameters);
	}

	/**
		Insert in DB
	*/
	function insertCriteriaMark($criteria_id, $evaluation_id, $mark_name, $mark, $mark_description, $checked)
	{
		$query = "INSERT INTO 
					`criteria_mark` (`id_criteria`, `id_evaluation_form`, `name`, `mark`, `description`, `activate`) 
					VALUES 			(:criteria_id, :id_eval, :name, :mark, :description, :checked)";

		$parameters = array(':criteria_id' 		=> $criteria_id,
							':id_eval' 			=> $evaluation_id,
							':name'				=> $mark_name,
							':mark' 			=> $mark,
							':description' 		=> $mark_description,
							':checked' 			=> $checked);

		$this->executePreparedQuery($query, $parameters);
	}

	/**
		Delete criteria
	*/
	function deleteCriteria($id_criteria)
	{
		$query = "DELETE FROM `criteria` WHERE `criteria`.`id` = :id";
		$parameters = array(':id' => $id_criteria);
		$this->executePreparedQuery($query, $parameters);

		$query = "DELETE FROM `criteria_mark` WHERE `criteria_mark`.`id_criteria` = :id";
		$parameters = array(':id' => $id_criteria);
		$this->executePreparedQuery($query, $parameters);		
	}

	/**
		Delete Level Mark
	*/
	function deleteLevelMark($name, $id_evaluation)
	{
		$query = "DELETE FROM `criteria_mark` WHERE `criteria_mark`.`name` LIKE :name AND `criteria_mark`.`id_evaluation_form` = :id_eval";
		$parameters = array(':name' => $name, ':id_eval' => $id_evaluation);
		$this->executePreparedQuery($query, $parameters);
	}

	/**
		Get CC
	*/
	function getAllConsultant()
	{
		$query = "SELECT `consultant`.`name`, `consultant`.`id` FROM `consultant` WHERE `consultant`.`active`=1";
		return $this->executeQuery($query);
	}

	/**
		Add consultant in DB
	*/
	function addConsultant($name)
	{
		$query = 'INSERT INTO `consultant` (`name`) VALUES (:name)';
		$parameters = array(':name' => $name);
		$this->executePreparedQuery($query, $parameters);
	}

	/**
		Delete consultant form DB
	*/
	function delConsultant($id)
	{
		$query = "UPDATE `consultant` SET `consultant`.`active` = 0 WHERE `consultant`.`id` = :id";
		$parameters = array(':id' => $id);
		$this->executePreparedQuery($query, $parameters);
	}

	/**
		Set criteria at 1 
	*/
	function setToZero($id)
	{
		$query = "UPDATE `criteria` SET `criteria`.`set_to_zero` = 1 WHERE `criteria`.`id` = :id";
		$parameters = array(':id' => $id);
		$this->executePreparedQuery($query, $parameters);
	}

	function deleteEvaluation($id)
	{
		$query = "DELETE FROM `rating` WHERE `rating`.`id`=:id";
		$parameters = array(':id' => $id);
		$this->executePreparedQuery($query, $parameters);
	}

	/**
		Return the number of evaluation with the same name as in given in parameters
	*/
	function checkIfExist($evaluation_name)
	{
		$query = 'SELECT COUNT(*) AS `number` FROM `evaluation_form` WHERE `evaluation_form`.`name` LIKE :evaluation_name';
		$parameters = array(':evaluation_name' => $evaluation_name);
		return $this->executePreparedQuery($query, $parameters);
	}
}
?>